── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr 1.1.4 ✔ readr 2.1.5
✔ forcats 1.0.0 ✔ stringr 1.5.1
✔ ggplot2 3.5.2 ✔ tibble 3.3.0
✔ lubridate 1.9.4 ✔ tidyr 1.3.1
✔ purrr 1.0.4
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag() masks stats::lag()
ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors
library (googlesheets4)
library (googledrive)
Attaching package: 'googledrive'
The following objects are masked from 'package:googlesheets4':
request_generate, request_make
library (ggrepel)
library (lme4)
Loading required package: Matrix
Attaching package: 'Matrix'
The following objects are masked from 'package:tidyr':
expand, pack, unpack
# posts <- read_sheet("https://docs.google.com/spreadsheets/d/1dDv3Zib0TC9R5GzjACkN6MzsneCPp6w7BPEJ8Kcjp94/edit?gid=2035831840#gid=2035831840")
#
# folder <- drive_get("https://drive.google.com/drive/folders/1oD6VagXmO0fHFD0GF14P7RbVaXSlAPb_")
#
# csv_files <- drive_ls(path = folder) %>%
# filter(grepl("\\.csv$", name))
#
# # Read all CSVs into a list
# csv_data_list <- map2(
# .x = csv_files$id,
# .y = csv_files$name,
# .f = function(file_id, file_name) {
# temp_path <- tempfile(fileext = ".csv")
# drive_download(as_id(file_id), path = temp_path, overwrite = TRUE)
# read_csv(temp_path)
# }
# )
#
# names(csv_data_list) <- csv_files$name
#
# combined_df <- imap_dfr(
# .x = csv_data_list,
# .f = ~ mutate(.x, source_file = .y)
# )
#
#
#
# combined_df <- combined_df |>
# mutate(date_of_extract = str_sub(source_file, end = 10)) |>
# select(date_of_extract, everything())
#
# combined_df <- combined_df |>
# select(date_of_extract, post_text = Title, everything())
#
# posts <- posts |>
# mutate(date_of_post = str_sub(`Post Date`, end = 10)) |>
# fill(Week, .direction = "down") |>
# rename(post_text = `Post Text Caption`) |>
# select(date_of_post, post_text, Condition)
#
# combined_df <- combined_df |>
# mutate(post_text = str_replace(post_text, '^"', ''))
#
# combined_df <- combined_df |>
# mutate(post_text = str_sub(post_text, end = 30))
#
# posts <- posts |>
# mutate(post_text = str_sub(post_text, end = 30))
#
# posts
# write_csv(combined_df, "tva_combined.csv")
# write_csv(posts, "tva_posts.csv")
combined_df <- read_csv ("tva_combined.csv" )
Warning: One or more parsing issues, call `problems()` on your data frame for details,
e.g.:
dat <- vroom(...)
problems(dat)
Rows: 4645 Columns: 109
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (10): date_of_extract, post_text, Page name, Description, Publish time, ...
dbl (49): Post ID, Page ID, Duration (sec), Is crosspost, Is share, Views, R...
lgl (50): Languages, Custom labels, Funded content status, Data comment, 3-s...
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
posts <- read_csv ("tva_posts.csv" )
Rows: 32 Columns: 3
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (2): post_text, Condition
date (1): date_of_post
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
# fix a little file issue
combined_df <- combined_df %>%
mutate (date_of_extract = ifelse (date_of_extract == "Apr-30-202" , "2025-04-30" , date_of_extract))
combined_df <- combined_df |>
left_join (posts, by = join_by (post_text))
combined_df <- combined_df %>%
mutate (Condition = ifelse (is.na (Condition), "BAU" , Condition))
combined_df %>% count (Condition)
# A tibble: 3 × 2
Condition n
<chr> <int>
1 BAU 3842
2 TVA 415
3 Text Only 388
combined_df <- combined_df %>%
mutate (date_of_extract_proc = lubridate:: ymd (date_of_extract)) %>%
mutate (date_of_publication = lubridate:: mdy_hm (` Publish time ` ))
combined_df %>%
select (date_of_extract, date_of_extract_proc, date_of_publication, ` Publish time ` )
# A tibble: 4,645 × 4
date_of_extract date_of_extract_proc date_of_publication `Publish time`
<chr> <date> <dttm> <chr>
1 2025-07-31 2025-07-31 2025-07-31 13:01:00 07/31/2025 13:01
2 2025-07-31 2025-07-31 2025-07-31 10:50:00 07/31/2025 10:50
3 2025-07-31 2025-07-31 2025-07-31 05:01:00 07/31/2025 05:01
4 2025-07-31 2025-07-31 2025-07-30 12:25:00 07/30/2025 12:25
5 2025-07-31 2025-07-31 2025-07-30 07:16:00 07/30/2025 07:16
6 2025-07-31 2025-07-31 2025-07-29 13:01:00 07/29/2025 13:01
7 2025-07-31 2025-07-31 2025-07-29 09:02:00 07/29/2025 09:02
8 2025-07-31 2025-07-31 2025-07-28 13:15:00 07/28/2025 13:15
9 2025-07-31 2025-07-31 2025-07-28 07:25:00 07/28/2025 07:25
10 2025-07-31 2025-07-31 2025-07-26 07:01:00 07/26/2025 07:01
# ℹ 4,635 more rows
combined_df %>%
group_by (post_text) %>%
summarize (post_text = first (post_text),
date_first_posted = first (date_of_publication),
date_of_extract_last = last (date_of_extract),
n = n (),
condition = first (Condition),
mean_reactions = mean (Reactions, na.rm = TRUE ),
mean_comments = mean (Comments, na.rm = TRUE ),
mean_shares = mean (Shares, na.rm = TRUE ),
mean_views = mean (Views, na.rm = TRUE ),
mean_reach = mean (Reach, na.rm = TRUE ),
mean_impressions = mean (Impressions, na.rm = TRUE ),
link_clicks = mean (` Link Clicks ` , na.rm = TRUE )) %>%
write_csv ("all-facebook-posts-tva.csv" )
combined_df %>%
skimr:: skim ()
Data summary
Name
Piped data
Number of rows
4645
Number of columns
113
_______________________
Column type frequency:
character
11
Date
2
logical
50
numeric
49
POSIXct
1
________________________
Group variables
None
Variable type: character
date_of_extract
0
1.00
10
10
0
62
0
post_text
44
0.99
29
30
0
208
0
Page name
0
1.00
32
32
0
1
0
Description
4511
0.03
210
827
0
9
0
Publish time
0
1.00
14
16
0
232
0
Caption type
0
1.00
3
8
0
3
0
Permalink
0
1.00
54
109
0
4487
0
Post type
0
1.00
4
6
0
4
0
Date
0
1.00
8
8
0
1
0
source_file
0
1.00
44
86
0
64
0
Condition
0
1.00
3
9
0
3
0
Variable type: Date
date_of_post
3842
0.17
2025-02-25
2025-06-26
2025-04-15
31
date_of_extract_proc
0
1.00
2025-02-25
2025-07-31
2025-05-18
62
Variable type: logical
Languages
4645
0
NaN
:
Custom labels
4645
0
NaN
:
Funded content status
4645
0
NaN
:
Data comment
4645
0
NaN
:
3-second video views by top audience (F, 18-24)
4644
0
1
TRU: 1
3-second video views by country (Australia (AU))
4642
0
1
TRU: 3
3-second video views by country (British Virgin Islands (VG))
4641
0
1
TRU: 4
3-second video views by country (Greece (GR))
4642
0
1
TRU: 3
3-second video views by country (Portugal (PT))
4644
0
1
TRU: 1
3-second video views by country (Malawi (MW))
4644
0
1
TRU: 1
3-second video views by country (Pakistan (PK))
4642
0
1
TRU: 3
3-second video views by country (Philippines (PH))
4644
0
1
TRU: 1
3-second video views by country (Sweden (SE))
4644
0
1
TRU: 1
3-second video views by top audience (M, 18-24)
4645
0
NaN
:
3-second video views by country (United Kingdom (GB))
4645
0
NaN
:
3-second video views by country (Puerto Rico (PR))
4645
0
NaN
:
3-second video views by country (Japan (JP))
4645
0
NaN
:
3-second video views by country (Romania (RO))
4645
0
NaN
:
3-second video views by country (Netherlands (NL))
4645
0
NaN
:
3-second video views by country (Nigeria (NG))
4645
0
NaN
:
3-second video views by country (Turkey (TR))
4645
0
NaN
:
3-second video views by country (Italy (IT))
4645
0
NaN
:
3-second video views by country (New Zealand (NZ))
4645
0
NaN
:
3-second video views by country (Singapore (SG))
4645
0
NaN
:
3-second video views by country (Argentina (AR))
4645
0
NaN
:
3-second video views by country (Ukraine (UA))
4645
0
NaN
:
3-second video views by country (Fiji (FJ))
4645
0
NaN
:
3-second video views by country (Poland (PL))
4645
0
NaN
:
3-second video views by country (Nepal (NP))
4645
0
NaN
:
3-second video views by country (Austria (AT))
4645
0
NaN
:
3-second video views by country (Vietnam (VN))
4645
0
NaN
:
3-second video views by country (Peru (PE))
4645
0
NaN
:
3-second video views by country (Switzerland (CH))
4643
0
1
TRU: 2
3-second video views by country (Israel (IL))
4643
0
1
TRU: 2
3-second video views by country (Ireland (IE))
4643
0
1
TRU: 2
3-second video views by country (Malaysia (MY))
4643
0
1
TRU: 2
3-second video views by country (South Africa (ZA))
4643
0
1
TRU: 2
3-second video views by country (Saudi Arabia (SA))
4643
0
1
TRU: 2
3-second video views by country (Macedonia (MK))
4643
0
1
TRU: 2
3-second video views by country (Costa Rica (CR))
4643
0
1
TRU: 2
3-second video views by country (Guernsey (GG))
4644
0
1
TRU: 1
3-second video views by country (Cyprus (CY))
4643
0
1
TRU: 2
3-second video views by country (American Samoa (AS))
4643
0
1
TRU: 2
3-second video views by country (Jamaica (JM))
4643
0
1
TRU: 2
3-second video views by country (Norway (NO))
4643
0
1
TRU: 2
3-second video views by country (Serbia (RS))
4643
0
1
TRU: 2
3-second video views by country (Brazil (BR))
4643
0
1
TRU: 2
3-second video views by country (United Arab Emirates (AE))
4643
0
1
TRU: 2
3-second video views by country (Georgia (GE))
4644
0
1
TRU: 1
3-second video views by country (Hungary (HU))
4644
0
1
TRU: 1
Variable type: numeric
Post ID
0
1.00
1.105625e+15
3.170539e+13
1.052957e+15
1.080783e+15
1.103292e+15
1.125767e+15
1.186236e+15
▇▇▇▅▂
Page ID
0
1.00
1.000649e+14
2.471545e+06
1.000649e+14
1.000649e+14
1.000649e+14
1.000649e+14
1.000650e+14
▇▁▁▁▁
Duration (sec)
0
1.00
2.540000e+00
1.964000e+01
0.000000e+00
0.000000e+00
0.000000e+00
0.000000e+00
2.340000e+02
▇▁▁▁▁
Is crosspost
0
1.00
0.000000e+00
0.000000e+00
0.000000e+00
0.000000e+00
0.000000e+00
0.000000e+00
0.000000e+00
▁▁▇▁▁
Is share
0
1.00
1.000000e-02
1.000000e-01
0.000000e+00
0.000000e+00
0.000000e+00
0.000000e+00
1.000000e+00
▇▁▁▁▁
Views
32
0.99
7.463280e+03
1.238689e+04
3.570000e+02
1.982000e+03
3.289000e+03
6.752000e+03
9.291100e+04
▇▁▁▁▁
Reach
0
1.00
4.718620e+03
6.875600e+03
2.580000e+02
1.430000e+03
2.291000e+03
4.894000e+03
4.660000e+04
▇▁▁▁▁
Reactions, Comments and Shares
0
1.00
1.312000e+02
2.804700e+02
0.000000e+00
1.200000e+01
3.100000e+01
8.200000e+01
1.720000e+03
▇▁▁▁▁
Reactions
0
1.00
8.910000e+01
1.842200e+02
0.000000e+00
1.000000e+01
2.500000e+01
5.900000e+01
1.054000e+03
▇▁▁▁▁
Comments
0
1.00
9.960000e+00
4.209000e+01
0.000000e+00
0.000000e+00
0.000000e+00
3.000000e+00
4.280000e+02
▇▁▁▁▁
Shares
0
1.00
3.214000e+01
6.963000e+01
0.000000e+00
2.000000e+00
7.000000e+00
2.400000e+01
4.830000e+02
▇▁▁▁▁
Total clicks
0
1.00
2.885400e+02
1.064430e+03
0.000000e+00
1.800000e+01
5.300000e+01
1.430000e+02
1.108700e+04
▇▁▁▁▁
Link Clicks
504
0.89
7.138000e+01
1.911200e+02
1.000000e+00
8.000000e+00
2.000000e+01
5.700000e+01
1.774000e+03
▇▁▁▁▁
Other Clicks
57
0.99
1.995500e+02
8.500700e+02
1.000000e+00
8.000000e+00
2.300000e+01
8.200000e+01
8.765000e+03
▇▁▁▁▁
Matched Audience Targeting Consumption (Photo Click)
654
0.86
3.236000e+01
7.061000e+01
1.000000e+00
3.000000e+00
8.000000e+00
2.500000e+01
5.480000e+02
▇▁▁▁▁
Negative feedback from users: Hide all
4076
0.12
1.430000e+00
5.800000e-01
1.000000e+00
1.000000e+00
1.000000e+00
2.000000e+00
3.000000e+00
▇▁▅▁▁
Negative feedback from users: Hide
4521
0.03
1.310000e+00
4.700000e-01
1.000000e+00
1.000000e+00
1.000000e+00
2.000000e+00
2.000000e+00
▇▁▁▁▃
Seconds viewed
4511
0.03
5.532125e+04
8.121570e+04
1.531430e+03
2.530930e+03
6.538620e+03
1.797391e+05
1.908877e+05
▇▁▁▁▃
Average Seconds viewed
4511
0.03
6.220000e+00
2.880000e+00
3.360000e+00
3.650000e+00
5.580000e+00
1.008000e+01
1.236000e+01
▇▅▁▃▂
Estimated earnings (USD)
4513
0.03
0.000000e+00
0.000000e+00
0.000000e+00
0.000000e+00
0.000000e+00
0.000000e+00
0.000000e+00
▁▁▇▁▁
Ad CPM (USD)
4511
0.03
0.000000e+00
0.000000e+00
0.000000e+00
0.000000e+00
0.000000e+00
0.000000e+00
0.000000e+00
▁▁▇▁▁
Ad impressions
4511
0.03
0.000000e+00
0.000000e+00
0.000000e+00
0.000000e+00
0.000000e+00
0.000000e+00
0.000000e+00
▁▁▇▁▁
Impressions
4604
0.01
4.456320e+03
3.950580e+03
6.880000e+02
1.560000e+03
2.933000e+03
5.754000e+03
1.455600e+04
▇▃▁▁▂
Negative feedback from users
4336
0.07
2.000000e-01
5.300000e-01
0.000000e+00
0.000000e+00
0.000000e+00
0.000000e+00
3.000000e+00
▇▁▁▁▁
Unique negative feedback from users
4336
0.07
2.000000e-01
5.300000e-01
0.000000e+00
0.000000e+00
0.000000e+00
0.000000e+00
3.000000e+00
▇▁▁▁▁
Unique negative feedback from users: Hide all
4608
0.01
1.430000e+00
6.000000e-01
1.000000e+00
1.000000e+00
1.000000e+00
2.000000e+00
3.000000e+00
▇▁▅▁▁
Unique negative feedback from users: Hide
4637
0.00
1.000000e+00
0.000000e+00
1.000000e+00
1.000000e+00
1.000000e+00
1.000000e+00
1.000000e+00
▁▁▇▁▁
3-second video views by top audience (F, 35-44)
4635
0.00
4.164000e+02
7.904400e+02
3.100000e+01
3.200000e+01
4.550000e+01
6.275000e+01
1.921000e+03
▇▁▁▁▂
3-second video views by top audience (F, 45-54)
4635
0.00
4.792000e+02
9.100700e+02
2.000000e+01
2.400000e+01
5.750000e+01
1.037500e+02
2.210000e+03
▇▁▁▁▂
3-second video views by top audience (F, 55-64)
4635
0.00
2.956000e+02
5.650400e+02
1.700000e+01
2.000000e+01
2.950000e+01
4.425000e+01
1.373000e+03
▇▁▁▁▂
3-second video views by top audience (F, 65+)
4635
0.00
2.925000e+02
5.726800e+02
1.200000e+01
1.700000e+01
2.350000e+01
3.100000e+01
1.383000e+03
▇▁▁▁▂
3-second video views by top audience (F, 25-34)
4635
0.00
1.564000e+02
3.028700e+02
9.000000e+00
9.250000e+00
1.250000e+01
2.000000e+01
7.330000e+02
▇▁▁▁▂
3-second video views by top audience (M, 45-54)
4635
0.00
5.790000e+01
1.123300e+02
3.000000e+00
3.250000e+00
5.000000e+00
7.000000e+00
2.720000e+02
▇▁▁▁▂
3-second video views by top audience (M, 35-44)
4635
0.00
6.160000e+01
1.151600e+02
2.000000e+00
6.000000e+00
8.000000e+00
1.200000e+01
2.810000e+02
▇▁▁▁▂
3-second video views by top audience (M, 65+)
4635
0.00
5.300000e+01
1.036000e+02
2.000000e+00
3.000000e+00
3.500000e+00
8.500000e+00
2.520000e+02
▇▁▁▁▂
3-second video views by top audience (M, 55-64)
4636
0.00
5.156000e+01
9.750000e+01
1.000000e+00
2.000000e+00
3.000000e+00
4.000000e+00
2.260000e+02
▇▁▁▁▂
3-second video views by top audience (M, 25-34)
4640
0.00
3.880000e+01
4.904000e+01
1.000000e+00
4.000000e+00
4.000000e+00
9.200000e+01
9.300000e+01
▇▁▁▁▅
3-second video views by country (United States (US))
4635
0.00
1.856800e+03
3.570690e+03
9.900000e+01
1.070000e+02
1.875000e+02
2.790000e+02
8.658000e+03
▇▁▁▁▂
3-second video views by country (Canada (CA))
4636
0.00
1.911000e+01
3.141000e+01
2.000000e+00
3.000000e+00
4.000000e+00
4.000000e+00
7.500000e+01
▇▁▁▁▂
3-second video views by country (Mexico (MX))
4639
0.00
2.000000e+00
1.550000e+00
1.000000e+00
1.000000e+00
1.000000e+00
3.250000e+00
4.000000e+00
▇▁▁▁▃
3-second video views by country (South Korea (KR))
4638
0.00
1.570000e+00
9.800000e-01
1.000000e+00
1.000000e+00
1.000000e+00
2.000000e+00
3.000000e+00
▇▁▁▁▃
3-second video views by country (Taiwan (TW))
4640
0.00
1.000000e+00
0.000000e+00
1.000000e+00
1.000000e+00
1.000000e+00
1.000000e+00
1.000000e+00
▁▁▇▁▁
3-second video views by country (Spain (ES))
4641
0.00
2.000000e+00
1.150000e+00
1.000000e+00
1.000000e+00
2.000000e+00
3.000000e+00
3.000000e+00
▇▁▁▁▇
3-second video views by country (Germany (DE))
4641
0.00
5.250000e+00
4.920000e+00
1.000000e+00
1.000000e+00
5.000000e+00
9.250000e+00
1.000000e+01
▇▁▁▁▇
3-second video views by country (Kenya (KE))
4643
0.00
1.000000e+00
0.000000e+00
1.000000e+00
1.000000e+00
1.000000e+00
1.000000e+00
1.000000e+00
▁▁▇▁▁
3-second video views by country (Thailand (TH))
4641
0.00
2.500000e+00
5.800000e-01
2.000000e+00
2.000000e+00
2.500000e+00
3.000000e+00
3.000000e+00
▇▁▁▁▇
3-second video views by country (Lebanon (LB))
4643
0.00
1.000000e+00
0.000000e+00
1.000000e+00
1.000000e+00
1.000000e+00
1.000000e+00
1.000000e+00
▁▁▇▁▁
3-second video views by country (India (IN))
4641
0.00
2.500000e+00
1.730000e+00
1.000000e+00
1.000000e+00
2.500000e+00
4.000000e+00
4.000000e+00
▇▁▁▁▇
3-second video views by country (Montenegro (ME))
4643
0.00
1.000000e+00
0.000000e+00
1.000000e+00
1.000000e+00
1.000000e+00
1.000000e+00
1.000000e+00
▁▁▇▁▁
Variable type: POSIXct
date_of_publication
0
1
2025-02-17 08:02:00
2025-07-31 13:01:00
2025-04-17 07:24:00
225
only_tva_and_text <- combined_df %>%
filter (Condition == "TVA" | Condition == "Text Only" )